'''
版权：Copyright (c) 2019 China

创建日期：Thursday September 5th 2019
创建者：ymq(ymq) - <<email>>

修改日期: Thursday, 5th September 2019 9:29:34 am
修改者: ymq(ymq) - <<email>>

说明
 1、
'''
from server.pao_python.pao.service.data.mongo_db import MongoService, MongoFilter, C, N, F, as_date
from ...service.mongo_bill_service import MongoBillFilter
from ...service.buss_pub.personnel_organizational import UserType
from server.pao_python.pao.service.security.security_utility import SecurityConstant,get_current_account_id


class DisplayService(MongoService):
    '''测试服务'''

    business_area_collection = 'PT_Business_Area'  # 业务区划表名
    current_year = 2019  # 当前年份

    def __init__(self, db_addr, db_port, db_name,  db_user, db_pwd,inital_password, session):
        self.db_addr = db_addr
        self.db_port = db_port
        self.db_name = db_name
        self.db_user = db_user
        self.db_pwd = db_pwd
        self.inital_password = inital_password
        self.session = session

    def cal_age_test(self, condition, page, count):
        _filter=self.__org_stat_service_person()
        return _filter.filter_objects

####################### 公共部分 ##############################
    def set_session_area(self):
        '''设置session的区域id'''
        account_id=get_current_account_id(self.session)
        _filter=MongoBillFilter()
        _filter.match_bill(C('account_id')==account_id)\
               .lookup_bill('PT_Administration_Division','admin_area_id','id','div_info')\
               .graph_lookup('PT_Administration_Division','$div_info.parent_id','parent_id','id','all_admin')\
               .unwind('all_admin')\
               .inner_join_bill('PT_Business_Area','all_admin.id','admin_id','area_info')
        res=self.query(_filter,'PT_User')
        self.session[SecurityConstant.area] = res[0]['area_info']['id']
        return res[0]['area_info']['id']

    def get_current_user_area_id(self):
        '''通过session获取当前登录用户的区域id'''
        if SecurityConstant.area in list(self.session.keys()):
            return self.session[SecurityConstant.area]
        else:
            res=self.set_session_area()
            return res

    def ___create_list(self, obj_list):
        '''将数据转成列表
        eg : obj_list = [{'y':a},{'y':b},{'y':c}] >> [a,b,c]
        '''
        new_list = []
        if obj_list:
            for obj in obj_list:
                tep = list(obj.values())
                if tep:
                    new_list.append(tep[0])
        return new_list

    def __org_area(self):
        '''找到某业务区域内的所有user信息'''
        division_id=self.get_current_user_area_id()
        _filter = MongoBillFilter()
        _filter.match(C('id') == division_id)\
               .inner_join('PT_Administration_Division', 'admin_id', 'id', 'admin_info')\
               .graph_lookup('PT_Administration_Division', '$admin_info.parent_id', 'id', 'parent_id', 'all_admin')\
               .project({'admin_id': '$all_admin.id'})\
               .unwind('admin_id')\
               .inner_join_bill('PT_User', 'admin_id', 'admin_area_id', 'user_info')
        return _filter

    def __elder_group_by_age(self, _filter):
        _filter.add_fields({'age': ((F('year')-self.current_year)*(-1)).f})\
               .add_fields({'age_range': self.ao.switch([self.ao.case(((F('age') >= 50) & (F('age') < 60)), '50-59'),
                                                         self.ao.case(((F('age') >= 60) & (F('age') < 70)), '60-69'),
                                                         self.ao.case(((F('age') >= 70) & (F('age') < 80)), '70-79'),
                                                         self.ao.case(((F('age') >= 80) & (F('age') < 90)), '80-89'),
                                                         self.ao.case(((F('age') >= 90) & (F('age') < 100)), '90-99')], '100以上')})
        return _filter

    def _group_by_date(self, _filter,col_name,date_unit):
        '''按date进行分组统计时，对日期进行处理，处理后数据存为新的一列
        Args :
            col_name:待处理的日期列名
            date_unit：分组的日期粒度，三个值可选：日，周，月 [暂时仅支持日和月]
        '''
        if date_unit == '日':
            _filter.add_fields({'new_date': self.ao.date_to_string(col_name,'%Y-%m-%d')})
        elif date_unit == '月':
            _filter.add_fields({'new_date': self.ao.date_to_string(col_name,'%Y-%m')})
        return _filter

#######################福利院  统计部分  start ####################################
    def ___org_area_welfare(self):
        '''获取指定业务区域内的福利院'''
        _filter = self.__org_area()
        _filter.match(C('user_info.personnel_type') == UserType.Organizational)\
               .match(C('user_info.organization_info.personnel_category') == '福利院')
        return _filter

    def org_statistics_total_quantity(self, condition, page, count):
        '''按行政区划和用户类型统计福利院数量'''
        _filter = self.___org_area_welfare()
        _filter.group({'x': '$user_info.id'})\
               .count('y')
        res = self.query(_filter, self.business_area_collection)
        '''按行政区划和用户类型统计福利院数量'''
        _filter2 = self.___org_area_welfare()
        _filter2.group({'x': '$user_info.id'})\
               .count('y')
        res = res + self.query(_filter2, self.business_area_collection)
        '''福利院工作人员总人数'''
        _filter3 = self.__org_stat_service_person()
        _filter3.count('y')
        res = res + self.query(_filter3, self.business_area_collection)
        return res

    def org_statistics_check_in_total_quantity(self, condition, page, count):
        '''福利院入住总人数'''
        _filter = self.___org_area_welfare()
        _filter.inner_join_bill('PT_Hotel_Zone', 'user_info.id', 'organization_id', 'hotel_zone_info')\
               .inner_join_bill('PT_Bed', 'hotel_zone_info.id', 'area_id', 'bed_info')\
               .match(C('residents_id') != '')\
               .count('y')
        res = self.query(_filter, self.business_area_collection)
        return res

    def __org_stat_service_person(self):
        '''福利院工作人员信息'''
        _filter = self.___org_area_welfare()
        _filter.inner_join_bill('PT_User_Relationship', 'user_info.id', 'main_relation_people', 'relation_info')\
               .inner_join_bill('PT_User_Relationship_Type', 'relation_info.relationship_type_id', 'id', 'relation_type_info')\
               .match(C('relation_type_info.name') == '雇佣')\
               .inner_join_bill('PT_User', 'relation_info.subordinate_relation_people','id','servicer_info')
        return _filter

    def org_statistics_service_personal_total_quantity(self, condition, page, count):
        '''福利院工作人员总人数'''
        _filter = self.__org_stat_service_person()
        _filter.count('y')
        res = self.query(_filter, self.business_area_collection)
        return res

    def org_statistics_nature_quantity(self, condition, page, count):
        '''按性质统计福利院数量'''
        _filter = self.___org_area_welfare()
        _filter.group({'x': '$user_info.organization_info.organization_nature'}, [{'y': self.ao.summation(1)}])
        res = self.query(_filter, self.business_area_collection)
        return res

    def org_statistics_street_quantity(self, condition, page, count):
        '''按镇街统计福利院数量'''
        _filter = self.___org_area_welfare()
        _filter.group({'x': '$user_info.town'}, [{'y': self.ao.summation(1)}])
        res = self.query(_filter, self.business_area_collection)
        return res

    def org_statistics_welfare_centre_bed_quantity(self, condition, page, count):
        '''按照福利院计算床位数'''
        _filter = self.___org_area_welfare()
        _filter.inner_join_bill('PT_Hotel_Zone', 'user_info.id', 'organization_id', 'hotel_zone_info')\
               .inner_join_bill('PT_Bed', 'hotel_zone_info.id', 'area_id', 'bed_info')\
               .group({'x': '$user_info.name'}, [{'y': self.ao.summation(1)}])
        res = self.query(_filter, self.business_area_collection)
        return res

    def org_statistics_street_bed_quantity(self, condition, page, count):
        '''按照镇街计算床位数'''
        _filter = self.___org_area_welfare()
        _filter.lookup_bill('PT_Hotel_Zone', 'user_info.id', 'organization_id', 'hotel_zone_info')\
               .lookup_bill('PT_Bed', 'hotel_zone_info.id', 'area_id', 'bed_info')\
               .add_fields({'num':self.ao.size('$bed_info')})\
               .group({'x': '$user_info.town'}, [{'y': self.ao.summation('$num')}])
        res = self.query(_filter, self.business_area_collection)
        return res

    def org_statistics_live_bed_quantity(self, condition, page, count):
        '''按照床位状态计算床位数'''
        _filter = self.___org_area_welfare()
        _filter.inner_join_bill('PT_Hotel_Zone', 'user_info.id', 'organization_id', 'hotel_zone_info')\
               .inner_join_bill('PT_Bed', 'hotel_zone_info.id', 'area_id', 'bed_info')\
               .add_fields({'tep':self.ao.if_null('$bed_info.residents_id','null')})\
               .add_fields({'state': self.ao.switch([self.ao.case(F('tep') == 'null','空住'),
                                                        self.ao.case(F('tep')=='', '空住')], '入住')})\
               .group({'x': '$state'}, [{'y': self.ao.summation(1)}])
        res = self.query(_filter, self.business_area_collection)
        return res

    def org_statistics_education_personnel_quantity(self, condition, page, count):
        '''按学历统计工作人员数量'''
        _filter = self.__org_stat_service_person()
        _filter.unwind('servicer_info.qualification_info')\
               .inner_join_bill('PT_Qualification_Type', 'servicer_info.qualification_info.qualification_type_id', 'id', 'qualification_type_info')\
               .match(C('qualification_type_info.name') == '学历证明')\
               .group({'x': '$servicer_info.qualification_info.name'}, [{'y': self.ao.summation(1)}])
        res = self.query(_filter, self.business_area_collection)
        return res

    def __org_stat_elder(self):
        '''获取福利院入住的长者信息'''
        _filter = self.___org_area_welfare()
        _filter.inner_join_bill('PT_Hotel_Zone', 'user_info.id', 'organization_id', 'hotel_zone_info')\
               .inner_join_bill('PT_Bed', 'hotel_zone_info.id', 'area_id', 'bed_info')\
               .match((C('bed_info.residents_id') != None)|(C('bed_info.residents_id')!=''))\
               .inner_join_bill('PT_User', 'bed_info.residents_id', 'id', 'elder_info')
        return _filter

    def org_statistics_sex_personnel_quantity(self, condition, page, count):
        '''按性别统计福利院入住长者数量'''
        _filter = self.__org_stat_elder()
        _filter.group({'x': '$elder_info.personnel_info.sex'}, [{'y': self.ao.summation(1)}])
        res = self.query(_filter, self.business_area_collection)
        return res

    def org_statistics_age_personnel_quantity(self, condition, page, count):
        ''' 按年龄统计福利院入住长者数量'''
        _filter = self.__org_stat_elder()
        _filter.add_fields({'year': self.ao.year('$elder_info.personnel_info.date_birth')})
        _filter = self.__elder_group_by_age(_filter)
        _filter.group({'x': '$age_range'}, [{'y': self.ao.summation(1)}])
        res = self.query(_filter, self.business_area_collection)
        return res

    def org_statistics_ability_personnel_quantity(self, condition, page, count):
        '''按行为能力统计福利院入住长者数量'''
        _filter = self.__org_stat_elder()
        _filter.unwind('elder_info.qualification_info')\
               .inner_join_bill('PT_Qualification_Type', 'elder_info.qualification_info.qualification_type_id', 'id', 'qualification_type_info')\
               .match(C('qualification_type_info.name') == '行为能力')\
               .group({'x': '$elder_info.qualification_info.name'}, [{'y': self.ao.summation(1)}])
        res = self.query(_filter, self.business_area_collection)
        return res

    def __org_stat_appoint_elder(self):
        '''预约中的长者信息'''
        _filter = self.___org_area_welfare()
        _filter.inner_join_bill('PT_Reservation_Registration', 'user_info.id', 'organization_id', 'appoint_info')\
               .match(C('appoint_info.state') == '预约中')\
               .inner_join_bill('PT_User', 'appoint_info.elder_id', 'id', 'elder_info')
        return _filter
    
    def org_statistics_ability_subscribe_quantity(self,condition,page,count):
        '''按行为能力统计预约中的人数'''
        _filter = self.__org_stat_appoint_elder()
        _filter.unwind('elder_info.qualification_info')\
               .inner_join_bill('PT_Qualification_Type', 'elder_info.qualification_info.qualification_type_id', 'id', 'qualification_type_info')\
               .match(C('qualification_type_info.name') == '行为能力')\
               .group({'x': '$elder_info.qualification_info.name'}, [{'y': self.ao.summation(1)}])
        res = self.query(_filter, self.business_area_collection)
        return res

    def org_statistics_time_subscribe_quantity(self, condition, page, count):
        '''按时间统计预约中的人数'''
        _filter = self.__org_stat_appoint_elder()
        _filter = self._group_by_date(_filter,'$appoint_info.reservation_date','月')
        _filter.group({'x': '$new_date'},[{'y':self.ao.summation(1)}])\
               .sort({'x': 1})\
               .limit(12)
        res = self.query(_filter, self.business_area_collection)
        return res

    def org_statistics_sex_subscribe_quantity(self, condition, page, count):
        '''按性别统计预约中的人数'''
        _filter = self.__org_stat_appoint_elder()
        _filter.group({'x': '$elder_info.personnel_info.sex'}, [{'y': self.ao.summation(1)}])
        res = self.query(_filter, self.business_area_collection)
        return res

    def org_statistics_age_subscribe_quantity(self, condition, page, count):
        '''按性别年龄预约中的人数'''
        _filter = self.__org_stat_appoint_elder()
        _filter.add_fields({'year': self.ao.year('$elder_info.personnel_info.date_birth')})
        _filter = self.__elder_group_by_age(_filter)
        _filter.group({'x': '$age_range'}, [{'y': self.ao.summation(1)}])
        res = self.query(_filter, self.business_area_collection)
        return res

    def org_statistics_time_income_quantity(self, condition, page, count):
        '''按时间统计福利院收入'''
        pass
    
    def org_detail_welfare_centre_room_status(self, condition, page, count):
        '''获取房态图数据'''
        collection_name = 'PT_Hotel_Zone'
        _filter = MongoBillFilter()
        _filter\
            .match(C('bill_status') == 'valid')\
            .lookup_bill('PT_Hotel_Zone', 'upper_hotel_zone_id', 'id', 'upper_hotel_zone')\
            .lookup_bill('PT_Bed', 'id', 'area_id', 'bed_tmp')\
            .project({
                'bed_tmp._id': 0
            })\
            .add_fields({'upper': self.ao.array_filter('$upper_hotel_zone', 'up_', (F('$up_.bill_status') == 'valid').f)})\
            .add_fields({'bed': self.ao.array_filter('$bed_tmp', 'bed_', (F('$bed_.bill_status') == 'valid').f)})\
            .project({
                '_id': 0,
                'id': 1,
                'zone_name': 1,
                'zone_number': 1,
                'bed': 1,
                'hotel_zone_type_id': 1,
                'upper_hotel_zone_name': '$upper.zone_name',
                'upper_hotel_zone_id': '$upper.id',
                'hotel_zone_type_name': '$hotel_zone_type.name'
            })\
            .lookup_bill('PT_User', 'bed.residents_id', 'id', 'user_tmp')\
            .add_fields({'user': self.ao.array_filter('$user_tmp', 'user_', (F('$user_.bill_status') == 'valid').f)})\
            .graph_lookup('PT_Hotel_Zone', '$upper_hotel_zone_id', 'upper_hotel_zone_id', 'id', 'upper_element')\
            .add_fields({'down_element': []})\
            .project({
                'upper_element._id': 0,
                'user._id': 0,
                'user_tmp._id': 0
            })

        res = self.query(_filter, collection_name)

        return res
#######################福利院  统计部分  end ####################################

#######################服务商   部分   start ####################################

    def ___org_area_sep(self):
        '''获取指定业务区域内的服务商'''
        _filter = self.__org_area()
        _filter.match(C('user_info.personnel_type') == UserType.Organizational)\
               .match(C('user_info.organization_info.personnel_category') == '服务商')
        return _filter

    def sep_statistics_total_quantity(self, condition, page, count):
        '''统计服务商数量'''
        _filter = self.___org_area_sep()
        _filter.group({'x': '$user_info.id'})\
               .count('y')
        res = self.query(_filter, self.business_area_collection)
        res2 = self.sep_statistics_service_personal_quantity(condition, page, count)
        res3 = self.sep_statistics_service_quantity(condition, page, count)
        if res2:
            res.extend(res2)
        else:
            res.extend({'y':0})
        if res3:
            res.extend(res3)
        else:
            res.extend({'y':0})
        return res

    
        

    def sep_statistics_street_service_provider_quantity(self, condition, page, count):
        '''按镇街统计服务商数量'''
        _filter = self.___org_area_sep()
        _filter.group({'x': '$user_info.town'}, [{'y':self.ao.summation(1)}])
        res = self.query(_filter, self.business_area_collection)
        return res

    def sep_statistics_time_service_provider_quantity(self, condition, page, count):
        '''按时间统计服务商数量'''
        _filter = self.___org_area_sep()
        _filter = self._group_by_date(_filter,'$user_info.create_date','月')
        _filter.group({'x': '$new_date'},[{'y':self.ao.summation(1)}])\
               .sort({'x': 1})\
               .limit(12)
        res = self.query(_filter, self.business_area_collection)
        return res

    def __sep_stat_service_person(self):
        '''服务商工作人员信息'''
        _filter = self.___org_area_sep()
        _filter.inner_join_bill('PT_User_Relationship', 'user_info.id', 'main_relation_people', 'relation_info')\
               .inner_join_bill('PT_User_Relationship_Type', 'relation_info.relationship_type_id', 'id', 'relation_type_info')\
               .match(C('relation_type_info.name') == '雇佣')\
               .inner_join_bill('PT_User', 'relation_info.subordinate_relation_people', 'id', 'service_info')
        return _filter

    def sep_statistics_service_personal_quantity(self, condition, page, count):
        '''统计服务商的服务人员总数'''
        _filter = self.__sep_stat_service_person()
        _filter.group({'x': '$service_info.id'})\
               .count('y')
        res = self.query(_filter, self.business_area_collection)
        return res

    def __sep_stat_service_order(self):
        '''查询服务商订单信息'''
        _filter = self.___org_area_sep()
        _filter.inner_join_bill('PT_Service_Order', 'user_info.id', 'service_provider_id', 'order_info')
        return _filter

    def __sep_stat_service_record(self):
        '''查询服务商服务记录信息'''
        _filter = self.__sep_stat_service_order()
        _filter.inner_join_bill('PT_Service_Record', 'order_info.id', 'order_id', 'record_info')
        return _filter

    def sep_card_location_service_provider(self, condition, page, count):
        '''当前行政区划内的服务商基础信息及地理位置'''
        _filter=self.___org_area_sep()
        _filter.project({'_id':0,'x':'$user_info.organization_info.lon','y':'$user_info.organization_info.lat'
                        ,'value1':'$user_info.name','value2':'$user_info.organization_info.personnel_category'
                        ,'value3':'$user_info.organization_info.organization_nature','value4':'$user_info.organization_info.legal_person'
                        ,'value5':'$user_info.address','value6':'$user_info.telephone'})
        res = self.query(_filter, self.business_area_collection)
        return res

    def sep_statistics_service_quantity(self, condition, page, count):
        '''统计服务商总服务次数'''
        _filter = self.__sep_stat_service_record()
        _filter.count('y')
        print(_filter.filter_objects)
        res = self.query(_filter, self.business_area_collection)
        return res

    def sep_statistics_service_provider_score_top10_quantity(self, condition, page, count):
        '''服务记录评分前十的服务商'''
        _filter = self.__sep_stat_service_record()

        _filter.add_fields({'avg_score': self.ao.avg('$record_info.appraise_list.score')})\
               .group({'id': '$user_info.id', 'x': '$user_info.name'}, [{'y':self.ao.avg('$avg_score')}])\
               .sort({'y': -1})\
               .limit(10)\
               .project({'x': 1, 'y': 1})
        res = self.query(_filter, self.business_area_collection)
        return res

    def sep_statistics_nature_score_quantity(self, condition, page, count):
        '''按机构性质统计服务商服务记录评分'''
        _filter = self.__sep_stat_service_record()
        _filter.add_fields({'avg_score': self.ao.avg('$record_info.appraise_list.score')})\
               .group({'x': '$user_info.organization_info.organization_nature'}, [{'y':self.ao.avg('$avg_score')}])
        res = self.query(_filter, self.business_area_collection)
        if res:
            for result in res:
                result['y'] = round(float(result.get('y')),2)
        return res

    def sep_statistics_service_provider_service_personal_top10_quantity(self, condition, page, count):
        '''服务人员（服务对象）人数(前十的服务商'''
        _filter = self.__sep_stat_service_order()
        _filter.group({'org_id': '$user_info.id', 'org_name': '$user_info.name', 'purchase_id': '$order_info.purchase_id'})\
               .group({'x': '$org_name', 'id': '$org_id'}, [{'y': self.ao.summation(1)}])\
               .sort({'y': -1})\
               .limit(10)\
               .project({'x': 1, 'y': 1})
        res = self.query(_filter, self.business_area_collection)
        return res

    def sep_statistics_nature_service_personal_quantity(self, condition, page, count):
        '''按机构性质统计服务对象的人数'''
        _filter = self.__sep_stat_service_order()
        _filter.group({'org_nature': '$user_info.organization_info.organization_nature', 'purchase_id': '$order_info.purchase_id'})\
               .group({'x': '$org_nature'}, [{'y': self.ao.summation(1)}])
        res = self.query(_filter, self.business_area_collection)
        return res

    def sep_statistics_service_provider_service_top10_quantity(self, condition, page, count):
        '''按服务商统计服务次数，取排名前十'''
        _filter = self.__sep_stat_service_record()
        _filter.group({'org_id': '$user_info.id', 'org_name': '$user_info.name', 'record_id': '$record_info.id'})\
               .group({'x': '$org_name', 'id': '$org_id'}, [{'y': self.ao.summation(1)}])\
               .sort({'y': -1})\
               .limit(10)\
               .project({'x': 1, 'y': 1})
        res = self.query(_filter, self.business_area_collection)
        return res

    def sep_statistics_nature_service_quantity(self, condition, page, count):
        '''按机构性质统计服务次数'''
        _filter = self.__sep_stat_service_record()
        _filter.group({'org_nature': '$user_info.organization_info.organization_nature', 'record_id': '$record_info.id'})\
               .group({'x': '$org_nature'}, [{'y': self.ao.summation(1)}])
        res = self.query(_filter, self.business_area_collection)
        return res

    def sep_statistics_service_provider_income_top10_quantity(self, condition, page, count):
        '''按服务商统计服务收入，取排名前十'''
        _filter = self.__sep_stat_service_record()
        _filter.group({'x': '$user_info.name', 'id': '$user_info.id'}, [{'y': self.ao.summation('$record_info.valuation_amount')}])\
               .sort({'y': -1})\
               .limit(10)\
               .project({'x': 1, 'y': 1})
        res = self.query(_filter, self.business_area_collection)
        return res

    def sep_statistics_nature_income_quantity(self, condition, page, count):
        '''按机构性质统计服务收入'''
        _filter = self.__sep_stat_service_record()
        _filter.group({'x': '$user_info.organization_info.organization_nature'}, [{'y': self.ao.summation('$record_info.valuation_amount')}])
        res = self.query(_filter, self.business_area_collection)
        return res

    def __sep_detail_record(self, condition):
        '''获取当前服务商的所有服务记录'''
        keys = ['id']
        values = self.get_value(condition, keys)
        _filter = MongoBillFilter()
        _filter.match_bill(C('service_provider_id') == values['id'])\
               .inner_join_bill('PT_Service_Record', 'id', 'order_id', 'record_info')
        return _filter

    def sep_detail_total_quantity(self, condition, page, count):
        '''当前服务商服务总次数'''
        _filter = self.__sep_detail_record(condition)
        _filter.count('y')
        res = self.query(_filter, 'PT_Service_Order')
        return res

    def sep_detail_education_quantity(self, condition, page, count):
        '''当前服务商平均评价'''
        _filter = self.__sep_detail_record(condition)
        _filter.add_fields({'avg_score': self.ao.avg('$record_info.appraise_list.score')})\
               .group({}, [{'y': self.ao.avg('$avg_score')}])
        res = self.query(_filter, 'PT_Service_Order')
        return res

    def sep_detail_income_quantity(self, condition, page, count):
        '''当前服务商总费用'''
        _filter = self.__sep_detail_record(condition)
        _filter.group({}, [{'y': self.ao.summation('$record_info.valuation_amount')}])
        print(_filter.filter_objects)
        res = self.query(_filter, 'PT_Service_Order')
        return res

    def sep_detail_service_provider(self, condition, page, count):
        '''当前服务商明细卡片基础数据'''
        keys = ['id']
        values = self.get_value(condition, keys)
        _filter = MongoBillFilter()
        _filter.match_bill(C('id') ==values['id'])\
               .lookup_bill('PT_User_Relationship', 'id', 'main_relation_people', 'relation_info')\
               .lookup_bill('PT_User_Relationship_Type', 'relation_info.relationship_type_id', 'id', 'relation_type_info')\
               .lookup_bill('PT_Qualification_Type', 'qualification_info.qualification_type_id','id','qualification_type')\
                .unwind('qualification_info')\
               .match_bill(C('qualification_type.name') == '服务评级')\
               .add_fields({'filter_info': self.ao.array_filter('$relation_type_info','a',(F('$a.name') == '雇佣').f)})\
               .add_fields({'number': self.ao.size('$filter_info')})\
               .project({'_id': 0,'y2':'$name','y3':'$address','x':'$content','y4':'$number','y5':'$qualification_info.name'
                        ,'y7':'$organization_info.personnel_category','y8':'$organization_info.organization_nature'
                        ,'y9':'$organization_info.legal_person','y10':'$organization_info.telephone'})
        res = self.query(_filter, 'PT_User')
        return res

    def sep_detail_activity_picture_list(self, condition, page, count):
        '''当前服务商服务记录图片'''
        keys = ['id']
        values = self.get_value(condition, keys)
        _filter = MongoBillFilter()
        _filter.match(C('organization') == values['id'])\
            .project({'_id': 0,'y':'$photo'})\
            .unwind('y')
        res = self.query(_filter, 'PT_Activity')
        # res=self.___create_list(tep)
        return res

    def sep_map_location_service_provider(self, condition, page, count):
        '''服务商 星空图'''
        _filter = self.___org_area_sep()
        _filter.unwind('user_info.qualification_info')\
               .inner_join_bill('PT_Qualification_Type', 'user_info.qualification_info.qualification_type_id','id','qualification_type')\
               .match(C('qualification_type.name') == '服务评级')\
               .project({'_id': 0,'x':'$user_info.organization_info.lon','y':'$user_info.organization_info.lat'
                        , 'value':'$user_info.qualification_info.name','z':'$user_info.name','id':'$user_info.id'})
        res = self.query(_filter, self.business_area_collection)
        result = [{
            'mapType':'scatter',
            'name':'星空图',
            'data':res,
        }]
        return result

    def sep_detail_promotional_video_surveillance(self, condition, page, count):
        '''当前服务商宣传视频'''
        keys = ['id']
        values = self.get_value(condition, keys)
        _filter = MongoBillFilter()
        _filter.match_bill((C('link_obj_id') ==values['id'])&(C('media_type')=='视频'))\
               .project({'_id': 0,'y':'$content_url'})
        res = self.query(_filter, 'PT_Multi_Media')
        return res

    def sep_detail_video_surveillance(self, condition, page, count):
        '''当前服务商监控摄像头列表'''
        keys = ['id']
        values = self.get_value(condition, keys)
        _filter = MongoBillFilter()
        _filter.match_bill(C('link_obj_id') ==values['id'])\
               .project({'_id': 0,'x':'$url'})
        res = self.query(_filter, 'PT_Monitor')
        # res=self.___create_list(tep)
        return res
    
    def sep_detail_picture_list(self,condition, page, count):
        '''当前服务商图片列表'''
        keys = ['id']
        values = self.get_value(condition, keys)
        _filter = MongoBillFilter()
        _filter.match_bill((C('link_obj_id') ==values['id'])&(C('media_type')=='图片'))\
               .project({'_id': 0,'x':'$content_url'})
        tep = self.query(_filter, 'PT_Multi_Media')
        res=self.___create_list(tep)
        return res

#######################  服务商部分 end ####################################

########################长者 统计 start ###################################
    def __eld_area_basic(self):
        '''本行政区划内的60岁以上生存长者信息'''
        _filter = self.__org_area()
        _filter.match((C('user_info.personnel_type') == UserType.Personnel)
                     & (C('user_info.personnel_info.dead')==0)
                     & (C('user_info.personnel_info.personnel_category') == '长者'))\
               .add_fields({'year': self.ao.year('$user_info.personnel_info.date_birth')})\
               .add_fields({'age': ((F('year')-self.current_year)*(-1)).f})\
               .match(C('age') > 60)
        return _filter

    def eld_statistics_total_quantity(self, condition, page, count):
        '''统计年龄60岁以上的行政区划内的长者总数'''
        _filter = self.__eld_area_basic()
        _filter.count('y1')
        res = self.query(_filter,self.business_area_collection)
        res2 = self.eld_statistics_welfare_centre_elder_total_quantity(condition, page, count)
        if res2:
            res.extend(res2)
        else:
            res.extend({'y':0})
        if res:
            res.append({'y':(res[0]['y1']- res2[0]['y'])})
        return res

    def eld_statistics_time_elder_quantity(self, condition, page, count):
        '''按时间统计长者数量走势'''
        _filter=self.__eld_area_basic()
        _filter=self._group_by_date(_filter,'$user_info.reg_date','月')
        _filter.group({'x':'$new_date'},[{'y':self.ao.summation(1)}])\
               .sort({'x':-1})
        res = self.query(_filter,self.business_area_collection)
        return res
    
    def eld_statistics_time_elder_age(self, condition, page, count):
        '''按照时间年龄统计长者走势'''
        _filter=self.__eld_area_basic()
        _filter=self._group_by_date(_filter,'$user_info.reg_date','月')
        _filter=self.__elder_group_by_age(_filter)
        _filter.group({'x1':'$new_date','x2':'$age_range'},[{'y':self.ao.summation(1)}])\
               .sort({'x1':-1})
        res = self.query(_filter,self.business_area_collection)
        return res
    
    def eld_statistics_time_elder_sex(self, condition, page, count):
        '''按照时间性别统计长者走势'''
        _filter=self.__eld_area_basic()
        _filter=self._group_by_date(_filter,'$user_info.reg_date','月')
        _filter.group({'x1':'$new_date','x2':'$user_info.personnel_info.sex'},[{'y':self.ao.summation(1)}])\
               .sort({'x1':-1})
        res = self.query(_filter,self.business_area_collection)
        return res

    def eld_statistics_welfare_centre_elder_total_quantity(self, condition, page, count):
        '''统计年龄60岁以上的行政区划内的机构中的长者数量，同org_statistics_check_in_total_quantity结果'''
        res = self.org_statistics_check_in_total_quantity(condition, page, count)
        result = []
        if res:
            result.append({'y':res[0].get('y')})   
        return result

    def eld_statistics_community_elder_total_quantity(self, condition, page, count):
        '''统计年龄60岁以上的行政区划内的非机构养老中的长者数量'''
        total = self.eld_statistics_total_quantity(condition, page, count)
        welfare_amount = self.eld_statistics_welfare_centre_elder_total_quantity(condition, page, count)
        y = 0
        if total:
            if welfare_amount:
                y = total[0]['y']-welfare_amount[0]['y']
            else:
                y = total[0]['y']
        else:
            y = 0
        return [{'y': y}]

    def eld_statistics_street_elder_quantity(self, condition, page, count):
        '''按镇街统计长者数量'''
        _filter = self.__eld_area_basic()
        _filter.group({'x': '$user_info.town'},[{'y':self.ao.summation(1)}])
        res = self.query(_filter,self.business_area_collection)
        return res

    def eld_statistics_pension_mode_elder_quantity(self, condition, page, count):
        '''按照养老方式统计长者数量'''
        total = self.eld_statistics_total_quantity(condition, page, count)
        welfare_amount = self.eld_statistics_welfare_centre_elder_total_quantity(condition, page, count)
        return [{'x': '机构养老','y':welfare_amount[0]['y']},{'x':'非机构养老','y':total[0]['y']-welfare_amount[0]['y']}]

    def eld_statistics_sex_elder_quantity(self, condition, page, count):
        '''按照性别统计长者数量'''
        _filter = self.__eld_area_basic()
        _filter.group({'x': '$user_info.personnel_info.sex'},[{'y':self.ao.summation(1)}])
        res = self.query(_filter,self.business_area_collection)
        return res

    def eld_statistics_age_elder_quantity(self, condition, page, count):
        '''按照年龄统计长者数量'''
        _filter = self.__eld_area_basic()
        _filter.add_fields({'birth_year': self.ao.year('$user_info.personnel_info.birth_date')})
        _filter = self.__elder_group_by_age(_filter)
        _filter.group({'x': '$age_range'},[{'y':self.ao.summation(1)}])
        res = self.query(_filter,self.business_area_collection)
        return res

    def __eld_stat_physical_exam(self):
        '''长者体检数据'''
        _filter = self.__eld_area_basic()
        _filter.lookup_bill('PT_Physical_Exam_Record', 'user_info.id','user_id','all_exam_info')\
               .add_fields({'new_date': self.ao.maximum('$all_exam_info.physical_exam_time')})\
               .add_fields({'exam_info': self.ao.array_filter('$all_exam_info','a',(F('$a.physical_exam_time') == '$new_date').f)})\
               .unwind('exam_info')
        return _filter

    def __eld_stat_physical_exam_item(self, item_name):
        '''各种指标偏高的长者'''
        _filter = self.__eld_stat_physical_exam()
        _filter.match((C('exam_info.physical_exam_data.data_item') == item_name)&(C('exam_info.physical_exam_data.result') =='偏高'))
        return _filter

    def __eld_stat_physical_exam_item_record(self, item_name):
        '''各种指标偏高的长者的服务记录'''
        _filter = self.__eld_stat_physical_exam_item(item_name)
        _filter.lookup_bill('PT_Service_Order', 'user_info.id','purchaser_id','order_info')\
               .lookup_bill('PT_Service_Record', 'order_info.id','order_id','record_info')
        return _filter

    def __eld_stat_physical_exam_item_number(self, item_name):
        '''各指标偏高长者数量'''
        _filter = self.__eld_stat_physical_exam_item(item_name)
        _filter.count('y')
        print('>>>>>>>>>>',_filter.filter_objects)
        res = self.query(_filter,self.business_area_collection)
        return res

    def eld_statistics_protein_elder_quantity(self, condition, page, count):
        '''低密度蛋白偏高长者数量'''
        return self.__eld_stat_physical_exam_item_number('低密度蛋白')

    def eld_statistics_triglyceride_elder_quantity(self, condition, page, count):
        '''甘油三酯偏高长者数量'''
        return self.__eld_stat_physical_exam_item_number('甘油三脂')

    def eld_statistics_temperature_elder_quantity(self, condition, page, count):
        '''体温偏高长者数量'''
        return self.__eld_stat_physical_exam_item_number('体温')

    def eld_statistics_pulse_elder_quantity(self, condition, page, count):
        '''脉搏偏高长者数量'''
        return self.__eld_stat_physical_exam_item_number('心率')

    def eld_statistics_water_elder_quantity(self, condition, page, count):
        '''体水分率偏高长者数量'''
        return self.__eld_stat_physical_exam_item_number('体水份量')

    def eld_statistics_blood_pressure_elder_quantity(self, condition, page, count):
        '''血压偏高长者数量'''
        return self.__eld_stat_physical_exam_item_number('血压')

    def __eld_stat_physical_exam_item_record_count(self, item_name):
        '''各指标偏高的长者的服务次数'''
        _filter = self.__eld_stat_physical_exam_item_record(item_name)
        _filter.add_fields({'count': self.ao.size('$record_info')})\
               .group({}, [{'y':self.ao.summation('$count')}])
        res = self.query(_filter,self.business_area_collection)
        return res

    def eld_statistics_protein_service_quantity(self, condition, page, count):
        '''低密度蛋白偏高长者服务次数'''
        return self.__eld_stat_physical_exam_item_record_count('低密度蛋白')

    def eld_statistics_triglyceride_service_quantity(self, condition, page, count):
        '''甘油三酯偏高长者服务次数'''
        return self.__eld_stat_physical_exam_item_record_count('甘油三脂')

    def eld_statistics_temperature_service_quantity(self, condition, page, count):
        '''体温偏高长者服务次数'''
        return self.__eld_stat_physical_exam_item_record_count('体温')

    def eld_statistics_pulse_service_quantity(self, condition, page, count):
        '''脉搏偏高长者服务次数'''
        return self.__eld_stat_physical_exam_item_record_count('心率')

    def eld_statistics_water_service_quantity(self, condition, page, count):
        '''体水分率偏高长者服务次数'''
        return self.__eld_stat_physical_exam_item_record_count('体水份量')

    def eld_statistics_blood_pressure_service_quantity(self, condition, page, count):
        '''血压偏高长者服务次数'''
        return self.__eld_stat_physical_exam_item_record_count('血压')

    def __eld_stat_physical_exam_item_record_amount(self, item_name):
        '''各指标偏高的长者的服务总金额'''
        _filter = self.__eld_stat_physical_exam_item_record(item_name)
        _filter.add_fields({'amount': self.ao.summation('$record_info.valuation_amount')})\
               .group({}, [{'y':self.ao.summation('$amount')}])
        res = self.query(_filter,self.business_area_collection)
        return res

    def eld_statistics_protein_buy_money_quantity(self, condition, page, count):
        '''低密度蛋白偏高长者的服务金额'''
        return self.__eld_stat_physical_exam_item_record_amount('低密度蛋白')

    def eld_statistics_triglyceride_buy_money_quantity(self, condition, page, count):
        '''甘油三酯偏高长者的服务金额'''
        return self.__eld_stat_physical_exam_item_record_amount('甘油三脂')

    def eld_statistics_temperature_buy_money_quantity(self, condition, page, count):
        '''体温偏高长者的服务金额'''
        return self.__eld_stat_physical_exam_item_record_amount('体温')

    def eld_statistics_pulse_buy_money_quantity(self, condition, page, count):
        '''脉搏偏高长者的服务金额'''
        return self.__eld_stat_physical_exam_item_record_amount('心率')

    def eld_statistics_water_buy_money_quantity(self, condition, page, count):
        '''体水分率偏高长者的服务金额'''
        return self.__eld_stat_physical_exam_item_record_amount('体水份量')

    def eld_statistics_blood_pressure_buy_money_quantity(self, condition, page, count):
        '''血压偏高长者的服务金额'''
        return self.__eld_stat_physical_exam_item_record_amount('血压')

    def __eld_stat_record(self):
        '''行政区划内的长者的服务记录'''
        _filter = self.__eld_area_basic()
        _filter.lookup_bill('PT_Service_Order', 'user_info.id','purchaser_id','order_info')\
               .lookup_bill('PT_Service_Record', 'order_info.id','order_id','record_info')
        return _filter

    def eld_statistics_elder_service_top10_quantity(self, condition, page, count):
        '''按长者统计服务次数top10'''
        _filter = self.__eld_stat_record()
        _filter.add_fields({'count': self.ao.size('$record_info')})\
               .sort({'count': -1})\
               .limit(10)\
               .project({'_id': 0,'x':'$user_info.name','y':'$count'})
        res = self.query(_filter,self.business_area_collection)
        return res

    def eld_statistics_age_service_quantity(self, condition, page, count):
        '''按照长者年龄段统计服务次数'''
        _filter = self.__eld_stat_record()
        _filter.add_fields({'count': self.ao.size('$record_info')})\
               .add_fields({'birth_year': self.ao.year('$user_info.personnel_info.birth_date')})
        _filter = self.__elder_group_by_age(_filter)
        _filter.group({'x': '$age_range'}, [{'y': self.ao.summation('$count')}])
        res = self.query(_filter,self.business_area_collection)
        return res

    def eld_statistics_elder_buy_money_top10_quantity(self, condition, page, count):
        '''按长者统计服务金额top10'''
        _filter = self.__eld_stat_record()
        _filter.add_fields({'amount': self.ao.summation('$record_info.valuation_amount')})\
               .sort({'amount': -1})\
               .limit(10)\
               .project({'_id': 0,'x':'$user_info.name','y':'$amount'})
        res = self.query(_filter,self.business_area_collection)
        return res

    def eld_statistics_age_buy_money_quantity(self, condition, page, count):
        '''按照长者年龄段统计服务金额'''
        _filter = self.__eld_stat_record()
        _filter.add_fields({'amount': self.ao.summation('$record_info.valuation_amount')})\
               .add_fields({'birth_year': self.ao.year('$user_info.personnel_info.birth_date')})
        _filter = self.__elder_group_by_age(_filter)
        _filter.group({'x': '$age_range'}, [{'y': self.ao.summation('$amount')}])
        res = self.query(_filter,self.business_area_collection)
        return res

###############################长者 统计 end   ######################################################

################################综合 统计 start ####################################################
